"""
openpyxl Excel读取功能详细教程
专注于各种读取Excel文件的方法和技巧
"""

import openpyxl
from openpyxl import load_workbook
import os

# ==================== 1. 基本文件读取 ====================

def basic_reading():
    """基本文件读取操作"""
    print("\n📖 1. 基本文件读取")
    print("-" * 50)
    
    # 检查文件是否存在
    filename = "员工信息表.xlsx"
    if not os.path.exists(filename):
        print(f"❌ 文件 {filename} 不存在，请先运行创建示例")
        return None
    
    # 1.1 加载工作簿
    wb = load_workbook(filename)
    print(f"✅ 成功加载工作簿: {filename}")
    
    # 1.2 查看所有工作表
    sheet_names = wb.sheetnames
    print(f"📋 工作表列表: {sheet_names}")
    
    # 1.3 选择工作表
    ws = wb["员工信息"]  # 通过名称选择
    # ws = wb.active     # 选择活动工作表
    
    print(f"📊 当前工作表: {ws.title}")
    print(f"📈 表格尺寸: {ws.max_row}行 × {ws.max_column}列")
    
    return wb, ws

# ==================== 2. 单元格读取方法 ====================

def cell_reading_methods(ws):
    """各种单元格读取方法"""
    print("\n🔍 2. 单元格读取方法")
    print("-" * 50)
    
    # 2.1 使用坐标读取
    print("\n📍 方法1: 使用坐标读取")
    cell_a1 = ws['A1']
    print(f"A1单元格的值: {cell_a1.value}")
    print(f"A1单元格的坐标: {cell_a1.coordinate}")
    
    # 2.2 使用cell()方法读取
    print("\n📍 方法2: 使用cell()方法")
    cell_b2 = ws.cell(row=2, column=2)
    print(f"B2单元格的值: {cell_b2.value}")
    
    # 2.3 读取多个单元格
    print("\n📍 方法3: 读取单元格范围")
    range_cells = ws['A1':'E1']  # 第一行的所有单元格
    for row in range_cells:
        for cell in row:
            print(f"{cell.coordinate}: {cell.value}", end=" | ")
        print()
    
    # 2.4 读取整行或整列
    print("\n📍 方法4: 读取整行")
    row_2 = ws[2]  # 第二行
    for cell in row_2:
        print(f"{cell.coordinate}: {cell.value}", end=" | ")
    print()
    
    return ws

# ==================== 3. 迭代读取数据 ====================

def iterative_reading(ws):
    """迭代读取数据的方法"""
    print("\n🔄 3. 迭代读取数据")
    print("-" * 50)
    
    # 3.1 iter_rows() - 按行迭代
    print("\n📊 方法1: iter_rows() - 按行迭代")
    print("表头:")
    for row in ws.iter_rows(min_row=1, max_row=1, values_only=True):
        print(row)
    
    print("\n数据行:")
    for row in ws.iter_rows(min_row=2, values_only=True):
        print(row)
    
    # 3.2 iter_cols() - 按列迭代
    print("\n📊 方法2: iter_cols() - 按列迭代")
    print("第一列数据:")
    for cell in ws.iter_cols(min_col=1, max_col=1, min_row=2, values_only=True):
        for value in cell:
            print(value)
    
    # 3.3 使用enumerate获取行号
    print("\n📊 方法3: 带行号的迭代")
    for row_num, row in enumerate(ws.iter_rows(values_only=True), 1):
        print(f"第{row_num}行: {row}")
    
    return ws

# ==================== 4. 高级读取技巧 ====================

def advanced_reading_techniques(ws):
    """高级读取技巧"""
    print("\n🎯 4. 高级读取技巧")
    print("-" * 50)
    
    # 4.1 读取特定范围
    print("\n🎯 技巧1: 读取特定范围")
    data_range = ws['A2':'C5']  # A2到C5的范围
    for row in data_range:
        row_data = [cell.value for cell in row]
        print(row_data)
    
    # 4.2 读取公式结果
    print("\n🎯 技巧2: 读取公式结果")
    # 注意：需要设置data_only=True来读取公式计算结果
    # wb = load_workbook('文件.xlsx', data_only=True)
    
    # 4.3 读取合并单元格
    print("\n🎯 技巧3: 处理合并单元格")
    merged_ranges = ws.merged_cells.ranges
    if merged_ranges:
        print("合并单元格范围:")
        for merged_range in merged_ranges:
            print(f"  {merged_range}")
            # 合并单元格的值只在第一个单元格中
            main_cell = ws[merged_range.min_row][merged_range.min_col - 1]
            print(f"  主单元格值: {main_cell.value}")
    else:
        print("无合并单元格")
    
    # 4.4 读取样式信息
    print("\n🎯 技巧4: 读取样式信息")
    sample_cell = ws['A1']
    print(f"A1字体: {sample_cell.font}")
    print(f"A1填充: {sample_cell.fill}")
    print(f"A1对齐: {sample_cell.alignment}")
    
    return ws

# ==================== 5. 数据筛选和查询 ====================

def data_filtering_and_query(ws):
    """数据筛选和查询"""
    print("\n🔍 5. 数据筛选和查询")
    print("-" * 50)
    
    # 5.1 查找特定值的单元格
    print("\n🔍 方法1: 查找特定值")
    target_value = "技术部"
    found_cells = []
    
    for row in ws.iter_rows():
        for cell in row:
            if cell.value == target_value:
                found_cells.append((cell.coordinate, cell.value))
    
    print(f"找到包含'{target_value}'的单元格:")
    for coord, value in found_cells:
        print(f"  {coord}: {value}")
    
    # 5.2 条件筛选
    print("\n🔍 方法2: 条件筛选")
    print("工资大于10000的员工:")
    
    # 假设工资在第4列（D列）
    for row_num in range(2, ws.max_row + 1):
        salary_cell = ws.cell(row=row_num, column=4)
        if salary_cell.value and isinstance(salary_cell.value, (int, float)):
            if salary_cell.value > 10000:
                name_cell = ws.cell(row=row_num, column=1)
                print(f"  {name_cell.value}: {salary_cell.value}")
    
    # 5.3 读取特定模式的数据
    print("\n🔍 方法3: 读取特定列")
    columns_to_read = [1, 2]  # 读取第1列和第2列（姓名和部门）
    for row_num in range(1, ws.max_row + 1):
        row_data = []
        for col_num in columns_to_read:
            cell_value = ws.cell(row=row_num, column=col_num).value
            row_data.append(cell_value)
        print(f"第{row_num}行: {row_data}")
    
    return ws

# ==================== 6. 错误处理和边界情况 ====================

def error_handling_and_edge_cases():
    """错误处理和边界情况"""
    print("\n⚠️ 6. 错误处理和边界情况")
    print("-" * 50)
    
    # 6.1 处理不存在的文件
    print("\n⚠️ 情况1: 文件不存在")
    try:
        wb = load_workbook("不存在的文件.xlsx")
    except FileNotFoundError as e:
        print(f"❌ 文件不存在错误: {e}")
    
    # 6.2 处理不存在的单元格
    print("\n⚠️ 情况2: 不存在的单元格")
    try:
        wb = load_workbook("员工信息表.xlsx")
        ws = wb["员工信息"]
        
        # 安全读取单元格
        max_row = ws.max_row
        max_col = ws.max_column
        
        # 尝试读取超出范围的单元格
        if max_row > 0 and max_col > 0:
            safe_cell = ws.cell(row=max_row + 10, column=max_col + 10)
            print(f"安全读取超出范围的单元格: {safe_cell.value}")  # 返回None
        
    except Exception as e:
        print(f"❌ 错误: {e}")
    
    # 6.3 处理空单元格
    print("\n⚠️ 情况3: 空单元格处理")
    wb = load_workbook("员工信息表.xlsx")
    ws = wb["员工信息"]
    
    for row in ws.iter_rows(values_only=True):
        processed_row = ["空" if cell is None else cell for cell in row]
        print(processed_row)
    
    return wb

# ==================== 7. 实用读取函数 ====================

def practical_reading_functions():
    """实用读取函数"""
    print("\n🛠️ 7. 实用读取函数")
    print("-" * 50)
    
    def read_entire_sheet(filename, sheet_name):
        """读取整个工作表的数据"""
        wb = load_workbook(filename)
        ws = wb[sheet_name]
        
        data = []
        for row in ws.iter_rows(values_only=True):
            data.append(row)
        
        return data
    
    def read_specific_range(filename, sheet_name, range_str):
        """读取特定范围的数据"""
        wb = load_workbook(filename)
        ws = wb[sheet_name]
        
        data = []
        for row in ws[range_str]:
            row_data = [cell.value for cell in row]
            data.append(row_data)
        
        return data
    
    def find_cell_by_value(filename, sheet_name, target_value):
        """根据值查找单元格"""
        wb = load_workbook(filename)
        ws = wb[sheet_name]
        
        results = []
        for row in ws.iter_rows():
            for cell in row:
                if cell.value == target_value:
                    results.append({
                        'coordinate': cell.coordinate,
                        'value': cell.value,
                        'row': cell.row,
                        'column': cell.column
                    })
        
        return results
    
    # 使用示例
    print("\n🛠️ 示例1: 读取整个工作表")
    entire_data = read_entire_sheet("员工信息表.xlsx", "员工信息")
    for i, row in enumerate(entire_data, 1):
        print(f"第{i}行: {row}")
    
    print("\n🛠️ 示例2: 读取特定范围")
    range_data = read_specific_range("员工信息表.xlsx", "员工信息", "A1:C3")
    for row in range_data:
        print(row)
    
    print("\n🛠️ 示例3: 查找特定值")
    found_results = find_cell_by_value("员工信息表.xlsx", "员工信息", "技术部")
    for result in found_results:
        print(f"找到: {result}")
    
    return {
        'read_entire_sheet': read_entire_sheet,
        'read_specific_range': read_specific_range,
        'find_cell_by_value': find_cell_by_value
    }

# ==================== 8. 性能优化技巧 ====================

def performance_optimization():
    """性能优化技巧"""
    print("\n⚡ 8. 性能优化技巧")
    print("-" * 50)
    
    # 8.1 使用values_only提高性能
    print("\n⚡ 技巧1: 使用values_only")
    wb = load_workbook("员工信息表.xlsx")
    ws = wb["员工信息"]
    
    # 慢的方式（读取完整单元格对象）
    print("慢的方式:")
    for row in ws.iter_rows(min_row=1, max_row=3):
        for cell in row:
            print(cell.value, end=" | ")
        print()
    
    # 快的方式（只读取值）
    print("\n快的方式:")
    for row in ws.iter_rows(min_row=1, max_row=3, values_only=True):
        print(row)
    
    # 8.2 分批读取大数据
    print("\n⚡ 技巧2: 分批读取")
    batch_size = 100  # 每批读取100行
    total_rows = ws.max_row
    
    for start_row in range(1, total_rows + 1, batch_size):
        end_row = min(start_row + batch_size - 1, total_rows)
        batch_data = []
        
        for row in ws.iter_rows(min_row=start_row, max_row=end_row, values_only=True):
            batch_data.append(row)
        
        print(f"读取第{start_row}-{end_row}行，共{len(batch_data)}行数据")
    
    return wb

# ==================== 主程序 ====================

if __name__ == "__main__":
    print("🚀 openpyxl Excel读取功能详细教程")
    print("=" * 60)
    
    # 1. 基本文件读取
    result = basic_reading()
    if result:
        wb, ws = result
        
        # 2. 单元格读取方法
        cell_reading_methods(ws)
        
        # 3. 迭代读取数据
        iterative_reading(ws)
        
        # 4. 高级读取技巧
        advanced_reading_techniques(ws)
        
        # 5. 数据筛选和查询
        data_filtering_and_query(ws)
        
        # 6. 错误处理和边界情况
        error_handling_and_edge_cases()
        
        # 7. 实用读取函数
        practical_reading_functions()
        
        # 8. 性能优化技巧
        performance_optimization()
        
        # 关闭工作簿
        wb.close()
    
    print("\n" + "=" * 60)
    print("🎉 读取功能教程完成！")
    
    # 显示可用的Excel文件
    excel_files = [f for f in os.listdir('.') if f.endswith('.xlsx')]
    print("\n📁 当前目录的Excel文件:")
    for file in excel_files:
        print(f"  - {file}")